﻿Imports Microsoft.VisualBasic
Imports MySql.Data.MySqlClient
Imports System.Windows.Forms

Public Class ConexionMySQL
    Private connetionString As String = ""
    Public cnn As MySqlConnection
    Public tran As MySqlTransaction

    Public Sub New(ByVal servidor As String, ByVal nombreBD As String, ByVal usuario As String, ByVal clave As String)
        connetionString = "Data Source=" & servidor & ";Initial Catalog=" & nombreBD & ";User ID=" & usuario & ";Password=" & clave
        cnn = New MySqlConnection(connetionString)
    End Sub

    Public Sub New()

    End Sub

    Public Sub ExecNonQuery(cmd As MySqlCommand)
        Try
            SetConnection(cmd)
            If cnn.State = ConnectionState.Closed Then cnn.Open()
            cmd.ExecuteNonQuery()
            If IsNothing(tran) Then cnn.Close()
        Catch ex As Exception
            Throw New Exception("Error al hacer ExecNonQuery: Error " & Err.Number & Chr(13) & Err.Description, ex)
        End Try
    End Sub

    Public Sub BeginTransaction()
        If cnn.State = ConnectionState.Closed Then cnn.Open()
        Me.tran = Me.cnn.BeginTransaction()
    End Sub

    Public Sub CommitTransaction()
        If tran Is Nothing Then
            Throw New Exception("No se ha inicializado ninguna transacción")
        End If
        tran.Commit()
        cnn.Close()
    End Sub

    Public Sub RollbackTransaction()
        If tran Is Nothing Then
            Throw New Exception("No se ha inicializado ninguna transacción")
        End If
        tran.Rollback()
        cnn.Close()
    End Sub

    Public Sub SetConnection(ByRef cmd As MySqlCommand)
        cmd.Connection = Me.cnn
        If cnn.State = ConnectionState.Closed Then cnn.Open()
        If Not IsNothing(tran) Then cmd.Transaction = tran
    End Sub

    Public Function Conectar(ByVal servidor As String, ByVal nombreBD As String, ByVal usuario As String, ByVal clave As String) As MySqlConnection

        connetionString = "Data Source=" & servidor & ";Initial Catalog=" & nombreBD & ";User ID=" & usuario & ";Password=" & clave
        cnn = New MySqlConnection(connetionString)
        Try
            cnn.Open()
            MsgBox("Se conectó correctamente")
            Return cnn
            'cnn.Close()
        Catch ex As Exception
            MsgBox(Err.Description, MsgBoxStyle.Critical, "Error número: " & Err.Number)
        End Try

    End Function

    Public Function getScalar(ByVal cmd As MySqlCommand) As Object
        Try
            Dim escalar As Object
            SetConnection(cmd)
            If cnn.State = ConnectionState.Closed Then cnn.Open()
            escalar = cmd.ExecuteScalar()
            If IsNothing(tran) Then cnn.Close()
            Return escalar
        Catch ex As Exception
            Throw New Exception("ERROR AL OBTENER SCALAR <getScalar>. Error: " & Err.Number & Chr(13) & Err.Description, ex)
        End Try
    End Function

    Public Function getResultados(ByVal cmd As MySqlCommand) As MySqlDataReader
        Try
            Dim Dr As MySqlDataReader
            SetConnection(cmd)
            Dr = cmd.ExecuteReader()
            'cnn.Close() Si lo cierro me tira error cuando lo retorno y quiero usarlo
            Return Dr
        Catch ex As Exception
            Throw New Exception("ERROR AL OBTENER RESULTADOS <getResultados>. Error: " & Err.Number & Chr(13) & Err.Description, ex)
        End Try
    End Function

    Public Sub Cerrar()
        cnn.Close()
    End Sub

    Public Sub LlenarGrilla(ByVal Grilla As DataGridView, ConsultaSql As String)
        Dim ds As New DataSet
        Dim da As New MySqlDataAdapter

        da.SelectCommand = New MySqlCommand(ConsultaSql, cnn)
        da.Fill(ds)
        cnn.Close()

        'con esta linea obtengo un objeto DataTable (dt) con los registros resultantes
        Dim dt As DataTable = ds.Tables(0)

        'Ahora puedo asignar el DataTable (dt) al DataGridView
        Grilla.DataSource = dt
    End Sub

    Public Sub CargarCombo(ByRef Combo As ComboBox, ByVal sql As String, Optional ByVal OpcionSeleccionar As Boolean = False, Optional ByVal OpcionTodos As Boolean = False)
        Dim Dt As DataTable
        Dim Da As New MySqlDataAdapter
        Dim Cmd As New MySqlCommand

        Try

            With Cmd
                .CommandType = CommandType.Text
                .CommandText = sql
                .Connection = Me.cnn
            End With

            Da.SelectCommand = Cmd
            Dt = New DataTable
            Da.Fill(Dt)

            If OpcionTodos = True Then
                Dim fila As DataRow = Dt.NewRow()
                fila(0) = 0
                fila(1) = "- TODOS -"
                Dt.Rows.InsertAt(fila, 0)
            End If

            If OpcionSeleccionar = True Then
                Dim fila As DataRow = Dt.NewRow()
                fila(0) = 0
                fila(1) = "Seleccionar..."
                Dt.Rows.InsertAt(fila, 0)
            End If

            With Combo
                .Text = ""
                .DataSource = Dt
                .DisplayMember = "Nombre"
                .ValueMember = "Id"
                '.Text = "Seleccionar..."
            End With

        Catch ex As Exception
            MsgBox(Err.Description, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Error: " & Err.Number)
        End Try

    End Sub
End Class

